import random
import string
from datetime import datetime, timedelta
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side, PatternFill
from openpyxl.utils import get_column_letter

# 生成指定长度的全小写随机字符串作为英文名
def generate_random_name(length):
    letters = string.ascii_lowercase  # 仅使用小写字母
    return ''.join(random.choice(letters) for i in range(length))

# 生成足够多不重复的英文名组合
english_names = []
while len(english_names) < 1000:
    new_name = generate_random_name(random.randint(5, 10))  # 生成 5 到 10 位的随机字符串
    if new_name not in english_names:
        english_names.append(new_name)

# 定义中文姓氏列表
chinese_surnames = '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜'
# 定义中文名字列表
chinese_names = '伟芳敏静刚强磊军洋勇艳杰娟涛明超秀霞丽平冬梅红莉'
# 定义部门列表
departments = ['employ', 'vendor', 'system', 'intern', 'test']
# 日期起始时间
date_start = datetime(2024, 2, 18)

# 存储所有行数据的列表
data = []

# 生成 1000 行数据
for i in range(1, 1001):
    # 按顺序使用不重复的英文名作为 ad 列的值
    ad = english_names[i - 1]
    # 随机选择一个中文姓氏和名字组合成中文姓名
    surname = random.choice(chinese_surnames)
    given_name = random.choice(chinese_names)
    name = surname + given_name
    # 生成年代数据
    decade = 2000 + (i % 10)
    # 随机选择一个部门
    department = random.choice(departments)

    # 随机决定账号过期时间是否为“永不过期”
    if random.random() < 0.3:
        account_expiry = "永不过期"
    else:
        account_expiry = (date_start + timedelta(days=i)).strftime('%Y/%m/%d')

    # 随机决定密码过期时间是否为“永不过期”
    if random.random() < 0.3:
        password_expiry = "永不过期"
    else:
        password_expiry = (date_start + timedelta(days=i + 1)).strftime('%Y/%m/%d')

    # 随机选择账号状态
    account_status = random.choice(['启用', '禁用'])

    # 打印每行数据
    print(f"{ad} {name} {decade} {department} {account_expiry} {password_expiry} {account_status}")

    # 将每行数据添加到 data 列表中
    data.append([ad, name, decade, department, account_expiry, password_expiry, account_status])

# 定义表头
headers = ["ad", "姓名", "年代", "部门", "账号过期时间", "密码过期时间", "账号状态"]

# 创建 DataFrame
df = pd.DataFrame(data, columns=headers)

# 保存到 Excel 文件
file_path = r'D:\test\test.xlsx'
df.to_excel(file_path, index=False)

# 加载工作簿
wb = load_workbook(file_path)
ws = wb.active

# 设置表头字体加粗，背景颜色为浅灰色
header_font = Font(bold=True)
header_fill = PatternFill(start_color="EEEEEE", end_color="EEEEEE", fill_type="solid")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 设置单元格边框
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
for row in ws.iter_rows():
    for cell in row:
        cell.border = thin_border

# 为数据行设置交替背景颜色（这里设置为浅蓝和白色交替）
for index, row in enumerate(ws.iter_rows(min_row=2, values_only=False), start=2):
    if index % 2 == 0:
        fill = PatternFill(start_color="E0F2F1", end_color="E0F2F1", fill_type="solid")
    else:
        fill = PatternFill(start_color="FFFFFF", end_color="FFFFFF", fill_type="solid")
    for cell in row:
        cell.fill = fill

# 自动调整列宽
for column in ws.columns:
    max_length = 0
    column_letter = get_column_letter(column[0].column)
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column_letter].width = adjusted_width

# 保存修改后的工作簿
wb.save(file_path)

print(f"数据已成功保存并美化到 {file_path}")